﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_sub_products
{
    public DAL_tbl_sub_products()
    {

    }



    public List<tbl_sub_products> select(int SUB_PRODUCT_ID)
    {
        List<tbl_sub_products> list = new List<tbl_sub_products>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select SUB_PRODUCT_ID,PRODUCT_ID,PURITY_ID,PSD_ID,SURFACE_AREA_ID,MILL_TYPE_ID,STOCK_IN_KG,IS_OUTSOURCE,OUTSOURCE_DETAILS,IS_ACTIVE,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE from tbl_sub_products where SUB_PRODUCT_ID=@SUB_PRODUCT_ID";
        cmd.Parameters.AddWithValue("@SUB_PRODUCT_ID", SUB_PRODUCT_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_sub_products()
            {
                SUB_PRODUCT_ID = dr["SUB_PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUB_PRODUCT_ID"]),
                PRODUCT_ID = dr["PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PRODUCT_ID"]),
                PURITY_ID = dr["PURITY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PURITY_ID"]),
                PSD_ID = dr["PSD_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PSD_ID"]),
                SURFACE_AREA_ID = dr["SURFACE_AREA_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SURFACE_AREA_ID"]),
                MILL_TYPE_ID = dr["MILL_TYPE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MILL_TYPE_ID"]),
                STOCK_IN_KG = dr["STOCK_IN_KG"] == DBNull.Value ? 0 : Convert.ToInt32(dr["STOCK_IN_KG"]),
                IS_OUTSOURCE = dr["IS_OUTSOURCE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["IS_OUTSOURCE"]),
                OUTSOURCE_DETAILS = dr["OUTSOURCE_DETAILS"] == DBNull.Value ? "" : dr["OUTSOURCE_DETAILS"].ToString(),
                IS_ACTIVE = dr["IS_ACTIVE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"] == DBNull.Value ? "" : dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"] == DBNull.Value ? DateTime.MinValue : dr["CREATED_DATE"].toDateTimeWithoutError(),
                MODIFIED_BY = dr["MODIFIED_BY"] == DBNull.Value ? "" : dr["MODIFIED_BY"].ToString(),
                MODIFIED_DATE = dr["MODIFIED_DATE"] == DBNull.Value ? DateTime.MinValue : dr["MODIFIED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_sub_products> selectAll()
    {
        List<tbl_sub_products> list = new List<tbl_sub_products>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select SUB_PRODUCT_ID,PRODUCT_ID,PURITY_ID,PSD_ID,SURFACE_AREA_ID,MILL_TYPE_ID,STOCK_IN_KG,IS_OUTSOURCE,OUTSOURCE_DETAILS,IS_ACTIVE,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE from tbl_sub_products Order By SUB_PRODUCT_ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_sub_products()
            {
                SUB_PRODUCT_ID = dr["SUB_PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUB_PRODUCT_ID"]),
                PRODUCT_ID = dr["PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PRODUCT_ID"]),
                PURITY_ID = dr["PURITY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PURITY_ID"]),
                PSD_ID = dr["PSD_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PSD_ID"]),
                SURFACE_AREA_ID = dr["SURFACE_AREA_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SURFACE_AREA_ID"]),
                MILL_TYPE_ID = dr["MILL_TYPE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MILL_TYPE_ID"]),
                STOCK_IN_KG = dr["STOCK_IN_KG"] == DBNull.Value ? 0 : Convert.ToInt32(dr["STOCK_IN_KG"]),
                IS_OUTSOURCE = dr["IS_OUTSOURCE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["IS_OUTSOURCE"]),
                OUTSOURCE_DETAILS = dr["OUTSOURCE_DETAILS"] == DBNull.Value ? "" : dr["OUTSOURCE_DETAILS"].ToString(),
                IS_ACTIVE = dr["IS_ACTIVE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"] == DBNull.Value ? "" : dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"] == DBNull.Value ? DateTime.MinValue : dr["CREATED_DATE"].toDateTimeWithoutError(),
                MODIFIED_BY = dr["MODIFIED_BY"] == DBNull.Value ? "" : dr["MODIFIED_BY"].ToString(),
                MODIFIED_DATE = dr["MODIFIED_DATE"] == DBNull.Value ? DateTime.MinValue : dr["MODIFIED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }


    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"select sp.SUB_PRODUCT_ID,pm.PRODUCT_NAME as PRODUCT_ID,py.PURITY_TYPE as PURITY_ID,p.PSD_TYPE as PSD_ID,
sa.SURFACE_AREA_TYPE as SURFACE_AREA_ID,mm.MILL_TYPE as MILL_TYPE_ID,sp.STOCK_IN_KG,
CASE WHEN sp.IS_OUTSOURCE =1 THEN 'True' ELSE 'False' END as 'IS_OUTSOURCE',sp.OUTSOURCE_DETAILS,
CASE WHEN sp.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',sp.CREATED_BY,sp.CREATED_DATE,sp.MODIFIED_BY,sp.MODIFIED_DATE 
from kanyogendra_inventory.tbl_sub_products sp 
LEFT JOIN kanyogendra_inventory.tbl_product_master pm
on pm.PRODUCT_ID =sp.PRODUCT_ID
LEFT JOIN kanyogendra_inventory.tbl_purity py
on py.PURITY_ID =sp.PURITY_ID
LEFT JOIN kanyogendra_inventory.tbl_psd_type p
on p.PSD_ID =sp.PSD_ID
LEFT JOIN kanyogendra_inventory.tbl_surface_area sa
on sa.SURFACE_AREA_ID =sp.SURFACE_AREA_ID
LEFT JOIN kanyogendra_inventory.tbl_mills_master mm
on mm.MILL_ID=sp.MILL_TYPE_ID Order By sp.SUB_PRODUCT_ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }


    public int insert(int PRODUCT_ID, int PURITY_ID, int PSD_ID, int SURFACE_AREA_ID, int MILL_TYPE_ID, int STOCK_IN_KG, int IS_OUTSOURCE, String OUTSOURCE_DETAILS, int IS_ACTIVE, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_sub_products(PRODUCT_ID, PURITY_ID, PSD_ID, SURFACE_AREA_ID, MILL_TYPE_ID, STOCK_IN_KG, IS_OUTSOURCE, OUTSOURCE_DETAILS, IS_ACTIVE, CREATED_BY, CREATED_DATE)
                        values(@PRODUCT_ID, @PURITY_ID, @PSD_ID, @SURFACE_AREA_ID, @MILL_TYPE_ID, @STOCK_IN_KG, @IS_OUTSOURCE, @OUTSOURCE_DETAILS, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@PRODUCT_ID", PRODUCT_ID);
        cmd.Parameters.AddWithValue("@PURITY_ID", PURITY_ID);
        cmd.Parameters.AddWithValue("@PSD_ID", PSD_ID);
        cmd.Parameters.AddWithValue("@SURFACE_AREA_ID", SURFACE_AREA_ID);
        cmd.Parameters.AddWithValue("@MILL_TYPE_ID", MILL_TYPE_ID);
        cmd.Parameters.AddWithValue("@STOCK_IN_KG", STOCK_IN_KG);
        cmd.Parameters.AddWithValue("@IS_OUTSOURCE", IS_OUTSOURCE);
        cmd.Parameters.AddWithValue("@OUTSOURCE_DETAILS", OUTSOURCE_DETAILS);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int SUB_PRODUCT_ID, int PRODUCT_ID, int PURITY_ID, int PSD_ID, int SURFACE_AREA_ID, int MILL_TYPE_ID, int STOCK_IN_KG, int IS_OUTSOURCE, String OUTSOURCE_DETAILS, int IS_ACTIVE, String MODIFIED_BY, DateTime MODIFIED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_sub_products SET PRODUCT_ID=@PRODUCT_ID, PURITY_ID=@PURITY_ID, PSD_ID=@PSD_ID, SURFACE_AREA_ID=@SURFACE_AREA_ID, MILL_TYPE_ID=@MILL_TYPE_ID, STOCK_IN_KG=@STOCK_IN_KG, IS_OUTSOURCE=@IS_OUTSOURCE, OUTSOURCE_DETAILS=@OUTSOURCE_DETAILS, IS_ACTIVE=@IS_ACTIVE, MODIFIED_BY=@MODIFIED_BY, MODIFIED_DATE=@MODIFIED_DATE WHERE SUB_PRODUCT_ID=@SUB_PRODUCT_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@SUB_PRODUCT_ID", SUB_PRODUCT_ID);
        cmd.Parameters.AddWithValue("@PRODUCT_ID", PRODUCT_ID);
        cmd.Parameters.AddWithValue("@PURITY_ID", PURITY_ID);
        cmd.Parameters.AddWithValue("@PSD_ID", PSD_ID);
        cmd.Parameters.AddWithValue("@SURFACE_AREA_ID", SURFACE_AREA_ID);
        cmd.Parameters.AddWithValue("@MILL_TYPE_ID", MILL_TYPE_ID);
        cmd.Parameters.AddWithValue("@STOCK_IN_KG", STOCK_IN_KG);
        cmd.Parameters.AddWithValue("@IS_OUTSOURCE", IS_OUTSOURCE);
        cmd.Parameters.AddWithValue("@OUTSOURCE_DETAILS", OUTSOURCE_DETAILS);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@MODIFIED_BY", MODIFIED_BY);
        cmd.Parameters.AddWithValue("@MODIFIED_DATE", MODIFIED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int SUB_PRODUCT_ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_sub_products WHERE SUB_PRODUCT_ID=@SUB_PRODUCT_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@SUB_PRODUCT_ID", SUB_PRODUCT_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}